-- @owner: zhanghuan
-- @date: 2024-06-19
-- @testpoint: 大数据量
--step1:大数据量; expect:执行成功
DROP TABLE IF EXISTS listagg_tab_over_3;
CREATE TABLE listagg_tab_over_3(
     COL_1 integer,
     COL_2 bigint,
     COL_3 float8,
     COL_4 decimal(12,6),
     COL_5 bool,
     COL_6 char(30),
     COL_7 varchar2(50),
     COL_8 varchar(30),
     COL_9 interval day to second,
     COL_10 TIMESTAMP,
     COL_11 date,
     COL_12 SMALLDATETIME,
     COL_13 TIMESTAMP WITHOUT TIME ZONE,
     COL_14 blob,
     COL_15 clob,
     COL_16 int[]
) WITH (STORAGE_TYPE=USTORE);

drop sequence if exists listagg_tab_over_3_seq;
create sequence listagg_tab_over_3_seq increment by 1 start with 10;
truncate table listagg_tab_over_3;

begin
        for i in 1..5000 loop
      insert into listagg_tab_over_3 values(
          i,
      listagg_tab_over_3_seq.nextval,
          i+445.255,
          98*0.99*i,
          true,
          lpad('abc','30','@'),
          lpad('abc','30','b'),
          rpad('abc','30','e'),
          (INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),
          to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
          to_timestamp('2019-01-03','YYYY-MM-DD '),
          to_timestamp('2019-01-03','YYYY-MM-DD '),
          to_timestamp('2019-01-03','YYYY-MM-DD '),
          lpad('10','12','01010')::blob,
          rpad('abc','9','a@123&^%djgk'),
          '{32,535,5645645,6767,76,67,56,48,979,978,7}'
          );

    end loop;
end;
/

select COL_11,COL_12,listagg(col_12,'hhh') WITHIN GROUP(order by case when col_1>col_2 then COL_11 else col_12 end nulls first,COL_12 nulls LAST)
over(partition by col_1) a from listagg_tab_over_3 order by 1,2,3 limit 10;

